package cn.edu.zhku.citrusmonitor.qa.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.edu.zhku.citrusmonitor.domain.TbAnswer;
import cn.edu.zhku.citrusmonitor.util.C3P0Util;

public class AnswerDaoImpl implements AnswerDao {
	
	
	BeanProcessor bean = new GenerousBeanProcessor();
	RowProcessor processor = new BasicRowProcessor(bean);
	@Override
	public void addA(TbAnswer answer) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql="insert into answer(answer_id,question_id,expert_id,responder,content) values(?,?,?,?,?)";
		qr.update(sql, answer.getAnswerId(),answer.getQuestionId(),answer.getExpertId(),answer.getResponder(),answer.getContent());
		String sql2="update question set status='已回复' where question_id=?";
		qr.update(sql2,answer.getQuestionId());
	}

	@Override
	public int removeA(String answer) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql="delete from answer where answer_id=?";
		
		
		return qr.update(sql, answer);
	}

	@Override
	public int modifyA(TbAnswer answer) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql="update answer set content=? where answer_id=?";
		
		
		return qr.update(sql, answer.getContent(),answer.getAnswerId());
	}
	//Ajax搜索框显示未回复的问题
	@Override
	public List<TbAnswer> findAByWord(String content) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql="select *from answer where 1=1";
		List<String> list=new ArrayList<String>();
		if(content!=null) {
			sql+=" and content like ?";
			
			list.add("%"+content+"%");			
		}
		
		
		return qr.query(sql,new BeanListHandler<TbAnswer>(TbAnswer.class,processor),list.toArray());
	}
	
	
	//得到总的数量
		@Override
		public int count(String content) throws SQLException {
			QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
			String sql="select count(*) from question";
			//如果content不是空，就把条件加上
			if(!"".equals(content)){
				sql+=" where summary like ? or content like ?";
			}
			long l = qr.query(sql, new ScalarHandler<Long>(1),"%"+content+"%","%"+content+"%");
			return (int)l;
			
		}

	//根据questionId搜索相应的答案
	@Override
	public TbAnswer showA(String questionId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql="select * from answer where question_id=?";
				
		return qr.query(sql,new BeanHandler<TbAnswer>(TbAnswer.class,processor),questionId);
	}
	//判断登录是否真实，根据expertid查找密码
	@Override
	public String findExpertPasswordById(int expertId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql="select password from expert where expert_id=?";
				
		return qr.query(sql,new ScalarHandler<>(1),expertId);
	}

	@Override
	public List<TbAnswer> showAll(int currentPage, int pageSize) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select * from answer order by create_time limit ?,?";
		

		return  qr.query(sql, new BeanListHandler<TbAnswer>(TbAnswer.class,processor),(currentPage-1)*pageSize,pageSize);
	}

	@Override
	public int delAll(String[] ids) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		Object[][] params = new Object[ids.length][];
		for (int i = 0; i < params.length; i++) {
			params[i] = new Object[]{ids[i]};//循环给每个一维数组中的元素赋值，值是id
		}
		qr.batch("delete from answer where answer_id=?", params);
		
		return 1;
	}
	
}
